//
//  DBAccess.m
//  SeaMelonSeeds_Counselor
//
//  Created by 杨笑 on 2017/9/22.
//  Copyright © 2017年 杨笑. All rights reserved.
//

#import "DBAccess.h"
#import <FMDB.h>
#import "Region.h"
@interface DBAccess()
@property (nonatomic, strong) FMDatabase *dataBase;
@end
 
@implementation DBAccess
+(DBAccess *)sharedInstance{
    static DBAccess *dbmanager;
    static dispatch_once_t dbManagerOnce;
    dispatch_once(&dbManagerOnce, ^{
        dbmanager = [[DBAccess alloc]init];
        [dbmanager openDatabase];
    });
    return dbmanager;
}

-(void)openDatabase{
    //获取Document目录路径
    NSString *documentsPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    //文件路径
    NSString *filePath = [documentsPath stringByAppendingPathComponent:@"sr.sqlite"];
    NSLog(@"%@",filePath);
    if (self.dataBase) {
        [self.dataBase close];
        self.dataBase = nil;
    }
    self.dataBase = [FMDatabase databaseWithPath:filePath];
    [self.dataBase open];

    //初始化表
    [self cityCreate];

}

//判断指定表是否存在
-(BOOL)checkTableExist:(NSString *)tableName{
    BOOL result = NO;
    NSString *lowtableName = [tableName lowercaseString];
    FMResultSet *rs = [self.dataBase executeQuery:@"SELECT [sql] FROM sqlite_master WHERE [type] = 'table' AND lower(name) = ?",lowtableName];
    result = [rs next];
    [rs close];
    return result;
}

//创建表
-(void)createTable:(NSString *)tableName sql:(NSString *)createSql{
    BOOL isExist = [self.dataBase tableExists:tableName];
    if (!isExist) {
        [self.dataBase executeUpdate:createSql];
    }
}

//删除表
-(BOOL)deleteWithTable:(NSString *)tableName{
    return [self.dataBase executeUpdate:[NSString stringWithFormat:@"DELETE FROM %@",tableName]];
}

/*
 *城市表
 *字段       类型           约束     备注
 id         INTEGER               主键
 pid        INTEGER               父id
 no         TEXT                  自己id
 name       TEXT                  名称
 level      INTEGER               级别
 code       TEXT
 type       TEXT
 sort       INTEGER
 py         TEXT
 matchs     TEXT
 is_default INTEGER                  是否默认城市
 is_service INTEGER                  是否开通
 */
-(void)cityCreate{
    [self createTable:@"city" sql:@"CREATE TABLE city (id INTEGER  PRIMARY KEY, pid INTEGER, no TEXT, name TEXT, level INTEGER, code TEXT, type TEXT, sort INTEGER, py TEXT, matchs TEXT, is_default INTEGER, is_service INTEGER);"];
    [self initCity];
}
-(void)initCity{
    int count = [self.dataBase intForQuery:@"SELECT Count(*) FROM city"];
    if (count > 0) {

        return;
    }

    NSError *error;
    NSString *insertSqlString = [NSString stringWithContentsOfFile:[[NSBundle mainBundle] pathForResource:@"citySqlString" ofType:@"txt"] encoding:NSUTF8StringEncoding error:&error];
    NSLog(@"Error reading text file. %@", [error localizedFailureReason]);
    for (NSString *str in [insertSqlString componentsSeparatedByString:@"//分割//"]) {
        [self.dataBase executeUpdate:str];
    }
}


/*
 *****数据库操作部分
 */

//城市操作
-(NSArray *)getAllCityByConditions:(NSString *)conditions{
    NSMutableArray *searchArray = [NSMutableArray new];
    FMResultSet *rs = [self.dataBase executeQuery:[NSString stringWithFormat:@"SELECT * FROM city %@",conditions]];
    while ([rs next]) {
        [searchArray addObject: [rs resultDictionary]];
    }
    [rs close];
    return searchArray;
}
-(NSArray *)searchOpenCity{
    return  [self getAllCityByConditions:@"WHERE is_service = 1"];
}
//更新所有城市为未开通
-(NSInteger)updateAllCityUnService{
    return [self.dataBase executeUpdate:@"UPDATE city SET is_service = 0"];
}
//更新指定城市已开通
-(NSInteger)updateCityServiceByCityId:(NSString *)cityId{
    return [self.dataBase executeUpdate:[NSString stringWithFormat:@"UPDATE city SET is_service = '1' WHEHE no = '%@'",cityId]];
}
//根据名称查找出区域model
-(Region *)getRegionByName:(NSString *)name{
    Region *region = [Region new];
    FMResultSet *rs = [self.dataBase executeQuery:@"SELECT * FROM city WHERE name = ?",name];
    if ([rs next]) {
        [region setValuesForKeysWithDictionary:[rs resultDictionary]];
    }
    [rs close];
    return region;
}

-(NSMutableArray *)getAllProvice{
    NSMutableArray *searchArray = [NSMutableArray new];
    FMResultSet *rs = [self.dataBase executeQuery:@"SELECT * FROM city WHERE level = 1"];
    while ([rs next]) {
        Region *region = [Region new];
        [region setValuesForKeysWithDictionary:[rs resultDictionary]];
        [searchArray addObject:region];
    }
    [rs close];
    return searchArray;
}

-(NSMutableArray *)getRegionByParentRegion:(Region *)pRegion{
    NSMutableArray *searchArray = [NSMutableArray new];
    FMResultSet *rs = [self.dataBase executeQuery:[NSString stringWithFormat:@"SELECT * FROM city WHERE pid = %ld",(long)pRegion.id]];
    while ([rs next]) {
        Region *region = [Region new];
        [region setValuesForKeysWithDictionary:[rs resultDictionary]];
        [searchArray addObject:region];
    }
    [rs close];
    return searchArray;
}

//根绝区域id返回区域名称
-(NSString *)getRegionNameById:(NSString *)regionId{
    FMResultSet *rs = [self.dataBase executeQuery:[NSString stringWithFormat:@"SELECT * FROM city WHERE id = %@",regionId]];
    NSString *name = @"";
    while ([rs next]) {
        name = [rs resultDictionary][@"name"];
    }
    [rs close];
    return name;
}
@end
